
INSERT INTO purchase_behaivor_history (purchase_id, provider_id, item_id ,inversion, profit, roi,date)
WITH 
VentaDetalle AS (

    SELECT 
        svi.purchase_vs_itemId,
        SUM((svi.qty - svi.returned_qty) * svi.price) as total_venta,
        SUM((svi.qty - svi.returned_qty) * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF((pvi.qty - pvi.returned_qty), 0)))) AS total_costo_venta
    FROM sale_vs_item svi
    inner join sale s on s.id = svi.saleId and s.deleted  = 0
    INNER JOIN purchase_vs_item pvi ON svi.purchase_vs_itemId = pvi.id
    inner join purchase p on p.id = pvi.purchaseId and p.deleted  = 0
    WHERE svi.deleted = 0
    GROUP BY svi.purchase_vs_itemId
 
),
PerdidaDetalle AS (
    -- 3. Valor de las mermas
    SELECT 
        purchase_vs_lost.purchase_vs_itemId,
        SUM(purchase_vs_lost.lost_price) AS valor_perdida
    FROM purchase_vs_lost
    inner join purchase_vs_item on purchase_vs_item.id = purchase_vs_lost.purchase_vs_itemId and  purchase_vs_item.deleted = 0
    inner join purchase on purchase.id = purchase_vs_item.purchaseId and  purchase_vs_item.deleted = 0
    WHERE purchase_vs_lost.deleted = 0 and purchase_vs_lost.return_item = ''
    GROUP BY purchase_vs_lost.purchase_vs_itemId
    
)
-- 4. Consolidación Final con Prorrateo
SELECT 
    p.id AS purchase_id,
    p.provider_id,
    pvi.item_id,
    -- Inversion Base (Costo Item + Gasto Directo de Fila)
	ROUND(
	    SUM(
	        pvi.qty * (
	            pvi.unit_price + 
	            COALESCE(
	                pvi.cogs_row_amount / NULLIF(pvi.qty - pvi.returned_qty, 0),
	                0
	            )
	        )
	    ),
	2) AS inversion_base,
	    
    -- Profit: Venta - Costo Vendido - Perdidas - (Gastos Generales Proporcionales)
    ROUND(SUM((
        COALESCE(v.total_venta, 0) - 
        COALESCE(v.total_costo_venta, 0) - 
        COALESCE(l.valor_perdida, 0) 
    )),2) AS profit_neto,
    
    -- ROI Neto: (Profit Neto / Inversion Base) * 100
	ROUND(CASE 
	    WHEN SUM(
	        pvi.qty * (
	            pvi.unit_price + 
	            COALESCE(
	                pvi.cogs_row_amount / NULLIF(pvi.qty - pvi.returned_qty, 0),
	                0
	            )
	        )
	    ) > 0 
	    THEN (
	        (
	            SUM(COALESCE(v.total_venta, 0)) 
	            - SUM(COALESCE(v.total_costo_venta, 0)) 
	            - SUM(COALESCE(l.valor_perdida, 0))
	        )
	        /
	        SUM(
	            pvi.qty * (
	                pvi.unit_price + 
	                COALESCE(
	                    pvi.cogs_row_amount / NULLIF(pvi.qty - pvi.returned_qty, 0),
	                    0
	                )
	            )
	        )
	    ) * 100
	    ELSE 0 
	end, 2) AS roi,
	   p.purchase_date AS date
FROM purchase p
INNER JOIN purchase_vs_item pvi ON p.id = pvi.purchaseId
LEFT JOIN VentaDetalle v ON pvi.id = v.purchase_vs_itemId
LEFT JOIN PerdidaDetalle l ON pvi.id = l.purchase_vs_itemId
WHERE p.deleted = 0 AND pvi.deleted = 0
group by 1,2,3,7
